package dao;

import bean.Cls;
import bean.Student;
import bean.Team;
import utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ClassDao {
    Connection conn= null;// 数据库连接
    ResultSet rs = null;
    PreparedStatement pstmt =null;
    int n=0;
    List<Cls> aList=null;
    List<Team> tList=null;
    List<Student> sList=null;
    Cls cls=new Cls();
    Team team2=new Team();
    Student student=new Student();

    public int insertClass(Cls a) {
        try {
            conn= DBUtil.getConnection();
            String sql="insert into class values (?,?)";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, a.getC_id());// 设置ID
            pstmt.setString(2, a.getC_name());// 设置名称
            n=pstmt.executeUpdate();
        } catch (Exception e) { // 打印异常
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);// 关闭资源
        }
        //创建一条通路
        return n;
    }

    public List<Cls> selectCls() {
        aList=new ArrayList();
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM class;";
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while(rs.next()){
                cls=new Cls();
                cls.setC_id(rs.getString("c_id"));
                cls.setC_name(rs.getString("c_name"));
                aList.add(cls);
            }
        } catch (Exception e) { // 打印异常
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);// 关闭资源
        }
        //创建一条通路
        return aList;
    }

    public List<Cls> selectClsByName(Cls cls2) {
        aList=new ArrayList();
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM class where c_name like ?;";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, "%"+cls2.getC_name()+"%");
            rs=pstmt.executeQuery();
            while(rs.next()){
                cls=new Cls();
                cls.setC_id(rs.getString("c_id"));
                cls.setC_name(rs.getString("c_name"));
                aList.add(cls);
            }
        } catch (Exception e) { // 打印异常
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);// 关闭资源
        }
        //创建一条通路
        return aList;
    }

    public int insertGroup(Team group) {
        try {
            conn=DBUtil.getConnection();
            String sql="insert into team values (?,?,?);";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, group.getC_id());
            pstmt.setString(2, group.getT_id());
            pstmt.setString(3, group.getT_name());
            n=pstmt.executeUpdate();
        } catch (Exception e) { // 打印异常
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);// 关闭资源
        }
        //创建一条通路
        return n;
    }

    public List<Team> selectTeam() {
        tList=new ArrayList();
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM team;";
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while(rs.next()){
                team2=new Team();
                team2.setC_id(rs.getString("c_id"));
                team2.setT_id(rs.getString("t_id"));
                team2.setT_name(rs.getString("t_name"));
                tList.add(team2);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);// 关闭资源
        }
        //创建一条通路
        return tList;
    }

    public List<Team> selectTeamByName(Team team) {
        tList=new ArrayList();
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM team where c_id=? and t_name like ?;";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, team.getC_id());
            pstmt.setString(2, "%"+team.getT_name()+"%");
            rs=pstmt.executeQuery();
            while(rs.next()){
                team2=new Team();
                team2.setC_id(rs.getString("c_id"));
                team2.setT_id(rs.getString("t_id"));
                team2.setT_name(rs.getString("t_name"));
                tList.add(team2);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);
        }
        //创建一条通路
        return tList;

    }

    public int deleteTeam(Team team) {
        System.out.println("deleteTeam...");
        try {
            conn=DBUtil.getConnection();
            String sql="delete from team where t_id=?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, team.getT_id());
            n=pstmt.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);// 关闭资源
        }
        //创建一条通路
        return n;
    }

    public Team selectTeamByName2(Team team) {
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM team where c_id=? and t_name like ?;";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, team.getC_id());
            pstmt.setString(2, "%"+team.getT_name()+"%");
            rs=pstmt.executeQuery();
            while(rs.next()){
                team2=new Team();
                team2.setC_id(rs.getString("c_id"));
                team2.setT_id(rs.getString("t_id"));
                team2.setT_name(rs.getString("t_name"));

            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);
        }
        //创建一条通路
        return team2;

    }

    public int insertStudent(Student student) {
        try {
            conn=DBUtil.getConnection();
            String sql="insert into student values (?,?,?,?);";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, student.getC_id());
            pstmt.setString(2, student.getT_id());
            pstmt.setString(3, student.getS_id());
            pstmt.setString(4, student.getS_name());
            n=pstmt.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);// 关闭资源
        }
        //创建一条通路
        return n;
    }

    public List<Student> selectStudent(Student student2) {
        System.out.println(student2);
        sList=new ArrayList();
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM student where c_id=? and s_id like ? or s_name like ?; ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, student2.getC_id());
            pstmt.setString(2, "%"+student2.getS_id()+"%");
            pstmt.setString(3, "%"+student2.getS_name()+"%");
            rs=pstmt.executeQuery();
            while(rs.next()){
                student=new Student();
                student.setC_id(rs.getString("c_id"));
                student.setT_id(rs.getString("t_id"));
                student.setS_id(rs.getString("s_id"));
                student.setS_name(rs.getString("s_name"));
                sList.add(student);
                System.out.println(sList);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);
        }
        //创建一条通路
        return sList;

    }

    public List<Student> selectStudentBysname(Student student2) {
        System.out.println(student2);
        sList=new ArrayList();
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM student where c_id=? and s_name like ? ; ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, student2.getC_id());
            pstmt.setString(2, "%"+student2.getS_name()+"%");

            rs=pstmt.executeQuery();
            while(rs.next()){
                student=new Student();
                student.setC_id(rs.getString("c_id"));
                student.setT_id(rs.getString("t_id"));
                student.setS_id(rs.getString("s_id"));
                student.setS_name(rs.getString("s_name"));
                sList.add(student);
                System.out.println(sList);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);
        }
        //创建一条通路
        return sList;
    }

    public List<Student> selectStudentBysid(Student student2) {
        System.out.println(student2);
        sList=new ArrayList();
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM student where c_id=? and s_id like ? ; ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, student2.getC_id());
            pstmt.setString(2, "%"+student2.getS_id()+"%");
            rs=pstmt.executeQuery();
            while(rs.next()){
                student=new Student();
                student.setC_id(rs.getString("c_id"));
                student.setT_id(rs.getString("t_id"));
                student.setS_id(rs.getString("s_id"));
                student.setS_name(rs.getString("s_name"));
                sList.add(student);
                System.out.println(sList);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);
        }
        //创建一条通路
        return sList;
    }

    public List<Student> selectStudentByall(Student student2) {
        System.out.println(student2);
        sList=new ArrayList();
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM student where c_id=? and s_id = ? and s_name = ? ; ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, student2.getC_id());
            pstmt.setString(2, student2.getS_id());
            pstmt.setString(3, student2.getS_name());
            rs=pstmt.executeQuery();
            while(rs.next()){
                student=new Student();
                student.setC_id(rs.getString("c_id"));
                student.setT_id(rs.getString("t_id"));
                student.setS_id(rs.getString("s_id"));
                student.setS_name(rs.getString("s_name"));
                sList.add(student);
                System.out.println(sList);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);
        }
        //创建一条通路
        return sList;
    }

    public Student selectStudent2(Student student2) {
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM student where c_id=? ORDER BY RAND() LIMIT 1 ; ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, student2.getC_id());
            rs=pstmt.executeQuery();
            while(rs.next()){
                student=new Student();
                student.setC_id(rs.getString("c_id"));
                student.setT_id(rs.getString("t_id"));
                student.setS_id(rs.getString("s_id"));
                student.setS_name(rs.getString("s_name"));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);
        }
        //创建一条通路
        return student;
    }

    public Team selectGroup(Team team) {
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM team where c_id=? ORDER BY RAND() LIMIT 1 ; ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, team.getC_id());
            rs=pstmt.executeQuery();
            while(rs.next()){
                team2=new Team();
                team2.setC_id(rs.getString("c_id"));
                team2.setT_id(rs.getString("t_id"));
                team2.setT_name(rs.getString("t_name"));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);
        }
        //创建一条通路
        return team2;
    }

    public Student selectStudentByGroup(Student studnet) {
        try {
            conn=DBUtil.getConnection();
            String sql="SELECT * FROM student where t_id=? ORDER BY RAND() LIMIT 1 ; ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, studnet.getT_id());
            rs=pstmt.executeQuery();
            while(rs.next()){
                student=new Student();
                student.setC_id(rs.getString("c_id"));
                student.setT_id(rs.getString("t_id"));
                student.setS_id(rs.getString("s_id"));
                student.setS_name(rs.getString("s_name"));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.close(rs, pstmt, conn);
        }
        //创建一条通路
        return student;
    }






}
